import 'package:account_book/common/constants/storage_key.dart';
import 'package:account_book/common/entity/history.dart';
import 'package:account_book/common/services/storage_service.dart';
import 'package:sqflite/sqflite.dart';
import 'base_dao.dart';



class HistoryDao extends BaseDao {

  HistoryDao._();
  static HistoryDao? _instance;
  static HistoryDao get instance => _instance ??= HistoryDao._();


  @override
  String tableName = "history";

  @override
  Future<void> onCreate(Database db, int version) async {
     await db.execute("""
      CREATE TABLE $tableName
      (
        id INTEGER PRIMARY KEY,
        bookId INTEGER NOT NULL,
        categoryId INTEGER NOT NULL,
        categoryName TEXT NOT NULL,
        amount REAL DEFAULT 0,
        remark TEXT NOT NULL,
        type INTEGER DEFAULT 1,
        createDate TEXT
      )
    """);

  }

  // 查询所有
  Future<List<History>> getAll() async {
    List<Map<String,dynamic>> list = await database.query(tableName);
    return List.generate(list.length, (index) {
      return History.fromMap(list[index]);
    });
  }

  // 根据日期查询
  Future<List<History>> queryListByDate(String startDate, String endDate) async {
    final bookId = StorageService.instance.getInt(StorageKeyConstants.currentBook) ?? 0;
    List<Map<String,dynamic>> list = await database.query(tableName,where: "bookId = ? and createDate >= ? and createDate<= ? order by createDate desc", whereArgs: [bookId, startDate,endDate]);
    return List.generate(list.length, (index) {
      return History.fromMap(list[index]);
    });
  }

  // 根据日期查询-按天聚合
  Future<List<Map<String,dynamic>>> queryGroupDayByDate(String startDate, String endDate,int type) async {
    final bookId = StorageService.instance.getInt(StorageKeyConstants.currentBook) ?? 0;
    String sql = 'select strftime("%d", createDate) as date, sum(amount) as total from $tableName where bookId = ? and type=? and createDate >= ? and createDate<= ? group by strftime("%Y-%m-%d", createDate)';
    List<Map<String,dynamic>> list = await database.rawQuery(sql, [bookId, type, startDate, endDate]);
    return list;
  }

  // 根据日期查询-按月聚合
  Future<List<Map<String,dynamic>>> queryGroupMonthByDate(String startDate, String endDate, int type) async {
    final bookId = StorageService.instance.getInt(StorageKeyConstants.currentBook) ?? 0;
    String sql = 'select strftime("%m", createDate) as date, sum(amount) as total from $tableName where bookId = ? and type=? and createDate >= ? and createDate<= ? group by strftime("%Y-%m", createDate)';
    List<Map<String,dynamic>> list = await database.rawQuery(sql, [bookId, type, startDate, endDate]);
    return list;
  }

  // 根据日期查询-按类别聚合
  Future<List<Map<String,dynamic>>> queryGroupCategoryByDate(String startDate, String endDate, int type) async {
    final bookId = StorageService.instance.getInt(StorageKeyConstants.currentBook) ?? 0;
    String sql = 'select categoryId, categoryName, sum(amount) as amount, count(*) as total from $tableName where bookId = ? and type=? and createDate >= ? and createDate<= ? group by categoryId order by amount desc';
    List<Map<String,dynamic>> list = await database.rawQuery(sql, [bookId, type, startDate, endDate]);
    return list;
  }

  // 根据日期查询-按月份聚合
  Future<List<Map<String,dynamic>>> countGroupMonthByDate(String startDate, String endDate) async {
    final bookId = StorageService.instance.getInt(StorageKeyConstants.currentBook) ?? 0;
    String sql = 'select strftime("%m", createDate) as date,sum(case when type == 1 then amount else 0 end) as expend, sum(case when type == 2 then amount else 0 end) as income from $tableName where bookId = ? and createDate >= ? and createDate<= ? group by strftime("%Y-%m", createDate)';
    List<Map<String,dynamic>> list = await database.rawQuery(sql, [bookId, startDate, endDate]);
    return list;
  }
}